package ro.gateway.aida.obj.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Hashtable;

import ro.gateway.aida.db.DBPersistenceManager;
import ro.gateway.aida.db.PersistenceToken;
import ro.gateway.aida.obj.GenericActivityProperty;
import ro.gateway.aida.srv.IIDGenerator;


/**
 * <p>Title: Romanian AIDA</p> <p>Description: :D application</p> <p>Copyright: Copyright (comparator) 2003</p>
 * <p>Company: Romania Development Gateway </p>
 *
 * @author Mihai Popoaei, mihai_popoaei@yahoo.com, smike@intellisource.ro
 * @version 1.0-* @version $Id: GenericActivityPropertyDB.java,v 1.1 2004/10/24 23:37:11 mihaipostelnicu Exp $
 */
public class GenericActivityPropertyDB extends DBPersistenceManager {
     private GenericActivityPropertyDB( PersistenceToken token ) {
          super( token );
     }

     public static GenericActivityPropertyDB getManager( PersistenceToken token ) {
          return new GenericActivityPropertyDB( token );
     }

     public void insert( IIDGenerator idGenerators,
                         GenericActivityProperty component,
                         String table_name )
        throws SQLException {


          String[] langs = component.getAvailableLanguages();
          if ( ( langs == null ) || ( langs.length < 1 ) ) {
               return;
          }

          AIDAActivityObjectDB.getManager( token ).insert( idGenerators, component );
          Connection con = getConnection();


          try {
               PreparedStatement ps = con.prepareStatement( INSERT1 + table_name + INSERT2 );

               for ( int i = 0; i < langs.length; i++ ) {
                    ps.clearParameters();
                    ps.setLong( 1, component.getId() );
                    ps.setString( 2, langs[ i ] );
                    ps.setString( 3, component.getName( langs[ i ] ) );
                    ps.executeUpdate();
               }

               ps.close();

          } catch ( SQLException sqlEx ) {
               throw sqlEx;
          } finally {
               con.close();
          }
     }

     public GenericActivityProperty getById( String table_name,
                                             long id )
        throws SQLException {

          Connection con = getConnection();
          GenericActivityProperty result = null;

          byte object_type = getObjectType( table_name );

          try {
               PreparedStatement ps = con.prepareStatement( GET_BY_ID1 + table_name + GET_BY_ID2 );
               ps.setLong( 1, id );
               ResultSet rs = ps.executeQuery();
               while ( rs.next() ) {
                    if ( result == null ) {
                         result = new GenericActivityProperty( object_type );
                         result.setId( id );
                    }

                    String lang = rs.getString( 1 );
                    result.setName( lang, rs.getString( 2 ) );
               }
               rs.close();
               ps.close();
          } catch ( SQLException sqlEx ) {
               throw sqlEx;
          } finally {
               con.close();
          }

          return result;
     }

     public void delete( String table_name,
                         long id )
        throws SQLException {

          Connection con = getConnection();

          try {
               PreparedStatement ps = con.prepareStatement( DELETE1 + table_name + DELETE2 );
               ps.setLong( 1, id );
               ps.executeUpdate();
               ps.close();

               AIDAActivityObjectDB
                  .getManager( db_token )
                  .delete( con, id );
          } catch ( SQLException sqlEx ) {
               throw sqlEx;
          } finally {
               con.close();
          }
     }

     private void deleteAIDAObjForActivity( String table_name,
                                            long activity_id )
        throws SQLException {

          byte object_type = getObjectType( table_name );
          Connection con = getConnection();
          try {
               PreparedStatement ps = con.prepareStatement( DELETE_OBJ_FOR_ACT );
               ps.setLong( 1, activity_id );
               ps.setByte( 2, object_type );
               ps.executeUpdate();
               ps.close();
          } catch ( SQLException sqlEx ) {
               throw sqlEx;
          } finally {
               con.close();
          }

          return;
     }

     public void deleteForActivity( String table_name,
                                    long activityId )
        throws SQLException {

          Connection con = getConnection();
          String database_pname = con.getMetaData().getDatabaseProductName();
          if ( 0 == database_pname.compareToIgnoreCase( "MYSQL" ) ) {
               System.out.println( "MYSQL - deleteForActivity (generic properties) -- DEBUG" );
               con.close();
               deleteForActivityMYSQL( table_name, activityId );
               return;
          }

          try {
               PreparedStatement ps = con.prepareStatement( DELETE_FOR_ACT1 + table_name + DELETE_FOR_ACT2 );
               ps.setLong( 1, activityId );
               ps.executeUpdate();
               ps.close();

               deleteAIDAObjForActivity( table_name, activityId );
          } catch ( SQLException sqlEx ) {
               throw sqlEx;
          } finally {
               con.close();
          }
     }

     private void deleteForActivityMYSQL( String table_name,
                                          long activityId )
        throws SQLException {

          GenericActivityProperty[] props = getAllForActivity( table_name, activityId );

          if ( props != null ) {
               Connection con = getConnection();
               try {
                    PreparedStatement ps = con.prepareStatement( "DELETE FROM " +
                       table_name +
                       " WHERE id = ?" );
                    for ( int i = 0; i < props.length; i++ ) {
                         ps.clearParameters();
                         ps.setLong( 1, props[ i ].getId() );
                         ps.executeUpdate();
                    }
                    ps.close();
                    ps = con.prepareStatement( "DELETE FROM objects " +
                       " WHERE id = ?" );
                    for ( int i = 0; i < props.length; i++ ) {
                         ps.clearParameters();
                         ps.setLong( 1, props[ i ].getId() );
                         ps.executeUpdate();
                    }
                    ps.close();
               } catch ( SQLException sqlEx ) {
                    throw sqlEx;
               } finally {
                    con.close();
               }
          }
     }

     public GenericActivityProperty[] getAllForActivity( String table_name,
                                                         long activity_id )
        throws SQLException {

          Connection con = getConnection();
          GenericActivityProperty[] result = null;
          ArrayList items = new ArrayList();
          byte object_type = getObjectType( table_name );

          try {
               PreparedStatement ps = con.prepareStatement( GET_ALL_FOR_ACTIVITY1 + table_name + GET_ALL_FOR_ACTIVITY2 );
               ps.setLong( 1, activity_id );
               ResultSet rs = ps.executeQuery();
               GenericActivityProperty last_item = null;
               while ( rs.next() ) {
                    long id = rs.getLong( 1 );
                    if ( ( last_item != null ) && ( last_item.getId() != id ) )
                         last_item = null;

                    if ( last_item != null ) {
                         String lang = rs.getString( 2 );
                         last_item.setName( lang, rs.getString( 3 ) );
                    } else {
                         last_item = new GenericActivityProperty( object_type );
                         last_item.setId( id );

                         String lang = rs.getString( 2 );
                         last_item.setName( lang, rs.getString( 3 ) );

                         items.add( last_item );
                    }
               }
               rs.close();
               ps.close();
          } catch ( SQLException sqlEx ) {
               throw sqlEx;
          } finally {
               con.close();
          }

          if ( items.size() > 0 ) {
               result = new GenericActivityProperty[ items.size() ];
               items.toArray( result );
          }

          return result;

     }

     private static byte getObjectType( String table_name ) {
          byte object_type = GenericActivityProperty.TYPE_GENERIC_UNKNOWN;
          Byte i_obj_type = ( Byte ) GENERIC_OBJECT_TYPES.get( table_name );
          if ( i_obj_type != null ) {
               object_type = i_obj_type.byteValue();
          }
          return object_type;
     }


     public GenericActivityProperty getFirstForActivity( String table_name,
                                                         long activity_id )
        throws SQLException {

          Connection con = getConnection();
          GenericActivityProperty result = null;
          byte object_type = getObjectType( table_name );

          try {
               PreparedStatement ps = con.prepareStatement( GET_ALL_FOR_ACTIVITY1 + table_name + GET_ALL_FOR_ACTIVITY2 );
               ps.setLong( 1, activity_id );
               ResultSet rs = ps.executeQuery();
               while ( rs.next() ) {
                    long id = rs.getLong( 1 );
                    if ( result == null ) {
                         result = new GenericActivityProperty( object_type );
                    } else {
                         if ( result.getId() != id ) {
                              break;
                         }
                    }

                    result.setId( rs.getLong( 1 ) );
                    String lang = rs.getString( 2 );
                    result.setName( lang, rs.getString( 3 ) );
               }
               rs.close();
               ps.close();
          } catch ( SQLException sqlEx ) {
               throw sqlEx;
          } finally {
               con.close();
          }

          // THIS IS B U G G Y
//			  if (result==null) return new GenericActivityProperty(Constants.DEFAULT_LANGUAGE,"FlatListItem from project "+ activity_id+" [Edit this text]");
          // THIS IS B U G G Y

          /*   if (result == null && "gen_titles".equals(table_name)) {
                       result = new GenericActivityProperty(object_type);
                       result.setName(Constants.DEFAULT_LANGUAGE, "Project title not defined (prj:" + activity_id + ")");
               }*/
          return result;

     }

     public static final String INSERT1 =
        "INSERT INTO ";
     public static final String INSERT2 =
        " (id, lang, name) " +
        "VALUES (?,?,?)";

     public static final String GET_BY_ID1 =
        "SELECT lang, name " +
        "FROM ";
     public static final String GET_BY_ID2 =
        " WHERE id = ?";

     public static final String DELETE1 =
        "DELETE FROM ";
     public static final String DELETE2 =
        " WHERE id = ?";
     public static final String DELETE_FOR_ACT1 =
        "DELETE FROM ";
     public static final String DELETE_FOR_ACT2 =
        " WHERE id IN (SELECT id FROM objects WHERE activity_id=?) ";
     // sterge obiectele de un anumit tip din tabela objects
     public static final String DELETE_OBJ_FOR_ACT =
        "DELETE FROM objects WHERE activity_id=? AND obj_type=?";


     public static final String GET_ALL_FOR_ACTIVITY1 =
        "SELECT c.id, c.lang, c.name " +
        "FROM ";
     public static final String GET_ALL_FOR_ACTIVITY2 =
        " c, objects o " +
        "WHERE o.activity_id=? AND o.id=c.id " +
        "ORDER BY c.id ASC, c.name ASC";

     private static final Hashtable GENERIC_OBJECT_TYPES =
        new Hashtable();

     static {
          GENERIC_OBJECT_TYPES.put( "gen_titles", new Byte( GenericActivityProperty.TYPE_TITLE ) );
          GENERIC_OBJECT_TYPES.put( "gen_ext_reasons", new Byte( GenericActivityProperty.TYPE_EXT_REASON ) );
          GENERIC_OBJECT_TYPES.put( "gen_descriptions", new Byte( GenericActivityProperty.TYPE_DESCRIPTION ) );
          GENERIC_OBJECT_TYPES.put( "gen_objectives", new Byte( GenericActivityProperty.TYPE_OBJECTIVE ) );
          GENERIC_OBJECT_TYPES.put( "gen_results", new Byte( GenericActivityProperty.TYPE_RESULT ) );
          GENERIC_OBJECT_TYPES.put( "gen_status", new Byte( GenericActivityProperty.TYPE_STATUS ) );
          GENERIC_OBJECT_TYPES.put( "gen_keywords", new Byte( GenericActivityProperty.TYPE_KEYWORDS ) );
          GENERIC_OBJECT_TYPES.put( "gen_components", new Byte( GenericActivityProperty.TYPE_COMPONENT ) );
     }
}
